package com.gxuwz.lhleavesys.dao.impl;

import com.gxuwz.lhleavesys.dao.ClassesDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.Classes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ClassesDaoImpl implements ClassesDao {

    @Autowired
    private DbUtil dbUtil;

    /**
     * 查询 BY ID
     * 
     * @param classID
     * @return
     */
    @Override
    public Classes findByClassID(String classID) {
        Classes classes = null;
        String sql = "SELECT * FROM sys_classes WHERE classID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classID);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    classes = new Classes(
                            rs.getString("classID"),
                            rs.getString("className"),
                            rs.getString("collegeID"),
                            rs.getString("major"),
                            rs.getString("grade"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return classes;
    }

    /**
     * 查詢所有
     * 
     * @return
     */
    @Override
    public List<Classes> findAll() {
        List<Classes> classList = new ArrayList<>();
        String sql = "SELECT * FROM sys_classes";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Classes classes = new Classes(
                        rs.getString("classID"),
                        rs.getString("className"),
                        rs.getString("collegeID"),
                        rs.getString("major"),
                        rs.getString("grade"));
                classList.add(classes);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return classList;
    }

    /**
     * 新增
     * 
     * @param classes
     * @return
     */
    @Override
    public boolean insert(Classes classes) {
        String sql = "INSERT INTO sys_classes (classID, className, collegeID, major, grade) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classes.getClassID());
            ps.setString(2, classes.getClassName());
            ps.setString(3, classes.getCollegeID());
            ps.setString(4, classes.getMajor());
            ps.setString(5, classes.getGrade());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新
     * 
     * @param classes
     * @return
     */
    @Override
    public boolean update(Classes classes, String oldClassID) {
        String sql = "UPDATE sys_classes SET classID = ?, className = ?, depID = ?, major = ?, grade = ? WHERE classID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classes.getClassID());
            ps.setString(2, classes.getClassName());
            ps.setString(3, classes.getCollegeID());
            ps.setString(4, classes.getMajor());
            ps.setString(5, classes.getGrade());
            ps.setString(6, oldClassID);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 刪除
     * 
     * @param classID
     * @return
     */
    @Override
    public boolean delete(String classID) {
        String sql = "DELETE FROM sys_classes WHERE classID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classID);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }

    @Override
    public boolean getClassesID(String classID) {
        boolean found = false;
        String sql = "SELECT COUNT(*) FROM sys_classes WHERE classID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classID);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    int count = rs.getInt(1);
                    found = count > 0;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return found;
    }

    @Override
    public List<Classes> BlurQuery(String classID, String className) {
        List<Classes> classeslist = new ArrayList<>();
        String sql = "SELECT * FROM sys_classes";

        StringBuilder sqlBuilder = new StringBuilder(sql);

        if (classID != null && !classID.isEmpty()) {
            sqlBuilder.append(" WHERE classID = ?");
            if (className != null && !className.isEmpty()) {
                sqlBuilder.append(" AND className = ?");
            }
        } else if (className != null && !className.isEmpty()) {
            sqlBuilder.append(" WHERE className = ?");
        }

        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sqlBuilder.toString())) {

            int parameterIndex = 1;

            if (classID != null && !classID.isEmpty()) {
                ps.setString(parameterIndex++, classID);
            }

            if (className != null && !className.isEmpty()) {
                ps.setString(parameterIndex++, className);
            }

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Classes classObj = new Classes(
                            rs.getString("classID"),
                            rs.getString("className"),
                            rs.getString("collegeID"),
                            rs.getString("major"),
                            rs.getString("grade"));
                    classeslist.add(classObj);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return classeslist;
    }

    @Override
    public List getAllClassesName() {
        List<Classes> classList = new ArrayList<>();
        String sql = "SELECT * FROM sys_classes";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Classes classes = new Classes(
                        rs.getString("className"));
                classList.add(classes);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return classList;
    }

    @Override
    public List<Classes> findMyselfClasses(String collegeID) {
        List<Classes> classList = new ArrayList<>();
        String sql = "SELECT * FROM sys_classes where depID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, collegeID);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Classes classes = new Classes(
                            rs.getString("classID"),
                            rs.getString("className"),
                            rs.getString("depID"),
                            rs.getString("major"),
                            rs.getString("grade"));
                    classList.add(classes);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return classList;
    }

}
